package com.lei.sqlJsp.dao.Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.lei.sqlJsp.dao.GoodsDao;
import com.lei.sqlJsp.entity.GoodsEntity;

public class GoodsDaoImpl implements GoodsDao {

	@Override
	public void add(GoodsEntity shopName) {
		Connection conn = JDBC.getConn();
		String sql = "insert into Table_myJsp(shop_number,shop_name,delete_logo,creator,mender) values(?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, shopName.getShopNumber());
			ps.setString(2, shopName.getShopName());
			ps.setString(3, shopName.getDeleteLogo() == null ? "N" : shopName.getDeleteLogo());
			ps.setString(4, shopName.getCreator());
			ps.setString(5, shopName.getMender());
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		JDBC.close(null, ps, conn);
	}

	@Override
	public void update(GoodsEntity id) {
		Connection conn = JDBC.getConn();
		String sql = "update Table_myJsp set shop_number=?,shop_name=? where id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, id.getShopNumber());
			ps.setString(2, id.getShopName());
			ps.setLong(3, id.getId());
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		JDBC.close(null, ps, conn);

	}

	@Override
	public List<GoodsEntity> listAll() {
		Connection conn = JDBC.getConn();
		String sql = "select id,shop_number,shop_name,delete_logo,creation_time,update_time,creator,mender  from Table_myJsp";
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<GoodsEntity> list = new ArrayList();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String shopNumber = rs.getString(i++);
				String shopName = rs.getString(i++);
				String deleteLogo = rs.getString(i++);
				Date createTime = rs.getDate(i++);
				Date updateTime = rs.getDate(i++);
				String creator =rs.getString(i++);
				String mender =rs.getString(i++);
				
				list.add(new GoodsEntity(id, shopNumber, shopName, deleteLogo, createTime, updateTime,creator,mender));
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public GoodsEntity getById(Long id) {
		Connection conn = JDBC.getConn();
		String sql = "select id,shop_number,shop_name,delete_logo,creation_time,update_time,creator,mender from Table_myJsp where id=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				int i = 1;
				GoodsEntity goods = new GoodsEntity();
				goods.setId(rs.getLong(i++));
				goods.setShopNumber(rs.getString(i++));
				goods.setShopName(rs.getString(i++));
				goods.setDeleteLogo(rs.getString(i++));
				goods.setCreationTime(rs.getDate(i++));
				goods.setUpdateTime(rs.getDate(i++));
				goods.setCreator(rs.getString(i++));
				goods.setMender(rs.getString(i++));
				return goods;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		JDBC.close(rs, ps, conn);
		return null;
	}

	@Override
	public void delete(Long id) {
		Connection conn = JDBC.getConn();
		String sql = "update Table_myJsp set delete_logo=? where id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, "Y");
			ps.setLong(2, id);
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		JDBC.close(null, ps, conn);

	}

	@Override
	public int getCount() {
		Connection conn = JDBC.getConn();
		String sql = "select count(1) from Table_myJsp where delete_logo='N'";
		Statement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		JDBC.close(rs, ps, conn);
		return 0;
	}

	@Override
	public List<GoodsEntity> getPage(int currentPage, int pageSize) {
		Connection conn = JDBC.getConn();
		String sql = "select id,shop_number,shop_Name,delete_logo,creation_time,update_time,creator,mender from Table_myJsp where delete_logo='N' limit ?,?";
		PreparedStatement ps = null; 
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			//1  0,5
			//2  5,5
			//3  10,5
			ps.setLong(1, (currentPage-1)*pageSize);
			ps.setLong(2, pageSize);
			rs = ps.executeQuery();
			List<GoodsEntity> list = new ArrayList<>();
			while (rs.next()) {
				int i = 1;
				GoodsEntity goods = new GoodsEntity();
				goods.setId(rs.getLong(i++));
				goods.setShopNumber(rs.getString(i++));
				goods.setShopName(rs.getString(i++));
				goods.setDeleteLogo(rs.getString(i++));
				goods.setCreationTime(rs.getDate(i++));
				goods.setUpdateTime(rs.getDate(i++));
				goods.setCreator(rs.getString(i++));
				goods.setMender(rs.getString(i++));
				list.add(goods);
				
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		JDBC.close(rs, ps, conn);
		return null;
	}

}
